To determine how changes made to a database have affected the results returned by a particular query, the particular query may be resubmitted, and the results produced by a previous execution of the query can be compared with the results produced by the current execution of the query. Unfortunately, the process of re-submitting a query and then comparing previous and current results may be tedious and time consuming, especially when many queries are involved. Further, in many circumstances, users would like to know about query result changes continuously, AS and WHEN the query result changes occur.
A variety of techniques may be used to inform users of query result changes. For example, according to a “trigger” approach, a trigger is created on each of the tables referenced in the queries of interest. One implementation of the trigger approach is described in U.S. Pat. No. 6,405,191, entitled “Publish and Subscribe in a Database System”. Inside the body of the trigger, the query is evaluated to detect if the result set has changed. For the trigger approach to be feasible, the trigger has to know the rows changed and the contents of the changed rows (before and after images). Because the after image is desired, the trigger approach will typically be implemented using triggers that fire after the change has been applied (an “after DML” trigger). However, a COMMIT trigger may also be used in conjunction with DML triggers. Specifically, DML triggers may be used to capture and buffer the row changes, and the COMMIT trigger may be used to send the buffered changes to the interested users.
Unfortunately, the trigger approach has several problems. For example, separate triggers have to be defined for each table that is being monitored for changes. Thus, one thousand triggers would be required to monitor one thousand tables. In addition, the trigger body execution has a significant cost since it is akin to executing a PL/SQL procedure. In the presence of a large number of queries, evaluating all the queries can be prohibitively expensive. This can significantly hamper the throughput (and increase latency) of OLTP transactions.
There are also visibility issues with DML triggers since the DML triggers are invoked while the transaction is still active. Invoking the DML trigger while the transaction is still alive may cause correctness issues, since the state of the database when the transaction is active may be significantly different compared to the state of the database at the time the transaction commits, thereby leading to incorrect evaluations. For the evaluation to be correct, the changes performed within a transaction have to be considered atomic as of the COMMIT POINT of the transaction. While visibility issues may be partly addressed by the use of COMMIT triggers, the problems of performance and scalability would still remain.
According to a log mining approach, logs may be used as a source of changes. The changes identified from the logs may be used to evaluate query result changes. The log mining approach can be implemented on top of a commercial DBMS using the log mining tools available. However, there is still a great cost to the log mining approach, both in terms of performance/scalability as well as complexity of logic.
Specifically, log mining has significant I/O costs, since log mining involves scanning redo logs to obtain log records. Every transaction has to be scanned, causing a great deal of I/O. Further, evaluating query result changes is also non-trivial, and evaluating every query will have severe scalability issues.
In a common usage scenario, the events of interest (i.e the query result changes) occur at a frequency much lower than the frequency of OLTP transaction commits i.e a large majority of transactions may not affect any query results at all. Moreover, even if a transaction is “interesting”, the transaction may affect only one or a few of possibly many thousands of registered queries. Checking query result changes for all possible queries for all possible transactions across all DML changes is not likely be a scalable solution.
Based on the foregoing, it is clearly desirable to provide a mechanism for identifying query result changes that avoids the scalability and performance issues of the trigger and log mining approaches.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.